package cdu.xjj.repository.service;

import cdu.xjj.pojo.entity.service.TestAssessmentSummary;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface TestAssessmentSummaryRepository extends JpaRepository<TestAssessmentSummary, Long> {

    /**
     * 获取今年完成体测数据上报的学生人数 , 规模为整个成都市学生
     * @param testYear 体测年度
     * @return
     */
    Long countByTestYear(Integer testYear);

    /**
     * 全成都市近五年的体测数据合格率
     * @param startYear 起始年份
     * @param endYear  结束年份(今年)
     */
    @Query(value = "SELECT test_year, " +
            "ROUND((SUM(IF(grade IN (0, 1, 2), 1, 0)) * 100.0 / COUNT(*)), 1) AS pass_rate " +
            "FROM tb_test_assessment_summary " +
            "WHERE test_year >= :startYear AND test_year <= :endYear " +
            "GROUP BY test_year " +
            "ORDER BY test_year", nativeQuery = true)
    List<Object[]> findPassRateByYearRange(int startYear, int endYear);

    /**
     * 特定学校近五年的体测数据合格率
     * @param startYear 起始年份
     * @param endYear   结束年份(今年)
     * @param schoolId 学生id
     */
    @Query(value = "SELECT tas.test_year, " +
            "ROUND((SUM(IF(tas.grade IN (0, 1, 2), 1, 0)) * 100.0 / COUNT(*)), 1) AS pass_rate " +
            "FROM tb_test_assessment_summary tas " +
            "JOIN tb_student s ON tas.student_id = s.id " +
            "JOIN tb_class c ON s.class_id = c.id " +
            "WHERE tas.test_year >= :startYear AND tas.test_year <= :endYear AND c.school_id = :schoolId " +
            "GROUP BY tas.test_year " +
            "ORDER BY tas.test_year", nativeQuery = true)
    List<Object[]> findPassRateByYearRangeAndSchoolId(int startYear, int endYear, Long schoolId);


    /**
     * 某学校本年度完成体测的学生人数
     * @param testYear 测试年份
     * @param schoolId 学校id
     */
    @Query(value = "SELECT COUNT(DISTINCT tas.student_id) " +
            "FROM tb_test_assessment_summary tas " +
            "JOIN tb_student s ON tas.student_id = s.id " +
            "JOIN tb_class c ON s.class_id = c.id " +
            "WHERE tas.test_year = :testYear AND c.school_id = :schoolId", nativeQuery = true)
    Long countStudentsWithTestsBySchoolIdAndYear(int testYear, Long schoolId);


    // 特定班级本年度成绩合格的学生人数
    @Query(value = "SELECT COUNT(DISTINCT tas.student_id) " +
            "FROM tb_test_assessment_summary tas " +
            "JOIN tb_student s ON tas.student_id = s.id " +
            "WHERE tas.test_year = :testYear AND tas.grade IN (0, 1, 2) AND s.class_id = :classId", nativeQuery = true)
    long countPassingStudentsByClassIdAndYear(int testYear, Long classId);


    // 特定班级本年度完成体测数据上报的学生人数
    @Query(value = "SELECT COUNT(DISTINCT tas.student_id) " +
            "FROM tb_test_assessment_summary tas " +
            "JOIN tb_student s ON tas.student_id = s.id " +
            "WHERE tas.test_year = :testYear AND s.class_id = :classId", nativeQuery = true)
    long countStudentsWithTestsByClassIdAndYear(int testYear, Long classId);


    /**
     * 查询特定学校指定年度的成绩 优秀 学生个数
     * @param schoolId 学校id
     * @param testYear 测试年度
     * @return 成绩 优秀 学生个数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "JOIN Clazz c ON s.classId = c.id " +
            "WHERE c.schoolId = :schoolId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 90.0")
    Long countExcellentStudentsBySchoolAndYear(@Param("schoolId") Long schoolId, @Param("testYear") Integer testYear);


    /**
     * 查询特定学校指定年度的成绩 良好 学生人数
     * @param schoolId 学校id
     * @param testYear 测试年度
     * @return  成绩 良好 学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "JOIN Clazz c ON s.classId = c.id " +
            "WHERE c.schoolId = :schoolId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 80.0 AND tas.totalScore <= 89.9")
    Long countGoodStudentsBySchoolAndYear(@Param("schoolId") Long schoolId, @Param("testYear") Integer testYear);


    /**
     * 查询特定学校指定年度的成绩 合格 的学生人数
     * @param schoolId 学校id
     * @param testYear 测试年度
     * @return 成绩 合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "JOIN Clazz c ON s.classId = c.id " +
            "WHERE c.schoolId = :schoolId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 60.0 AND tas.totalScore <= 79.9")
    Long countPassingStudentsBySchoolAndYear(@Param("schoolId") Long schoolId, @Param("testYear") Integer testYear);


    /**
     * 查询特定学校指定年度的成绩 不合格 的学生人数
     * @param schoolId 学校id
     * @param testYear 测试年度
     * @return 成绩 不合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "JOIN Clazz c ON s.classId = c.id " +
            "WHERE c.schoolId = :schoolId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore <= 59.9")
    Long countFailingStudentsBySchoolAndYear(@Param("schoolId") Long schoolId, @Param("testYear") Integer testYear);


    /**
     * 查询特定班级某一年度的体测成绩 优秀 的学生人数
     * @param classId 班级id
     * @param testYear  测试年度
     * @return 成绩 优秀 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "WHERE s.classId = :classId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 90.0")
    Long countExcellentStudentsByClassAndYear(@Param("classId") Long classId, @Param("testYear") Integer testYear);


    /**
     * 查询特定班级某一年度的体测成绩 良好 的学生人数
     * @param classId 班级id
     * @param testYear  测试年度
     * @return 成绩 良好 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "WHERE s.classId = :classId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 80.0 AND tas.totalScore <= 89.9")
    Long countGoodStudentsByClassAndYear(@Param("classId") Long classId, @Param("testYear") Integer testYear);


    /**
     * 查询特定班级某一年度的体测成绩 合格 的学生人数
     * @param classId 班级id
     * @param testYear  测试年度
     * @return 成绩 合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "WHERE s.classId = :classId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore >= 60.0 AND tas.totalScore <= 79.9")
    Long countPassingStudentsByClassAndYear(@Param("classId") Long classId, @Param("testYear") Integer testYear);


    /**
     * 查询特定班级某一年度的体测成绩 不合格 的学生人数
     * @param classId 班级id
     * @param testYear  测试年度
     * @return 成绩 不合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "JOIN Student s ON tas.studentId = s.id " +
            "WHERE s.classId = :classId " +
            "AND tas.testYear = :testYear " +
            "AND tas.totalScore <= 59.9")
    Long countFailingStudentsByClassAndYear(@Param("classId") Long classId, @Param("testYear") Integer testYear);


    /**
     * 查询成都市某一年度的体测成绩 优秀 的学生人数
     * @param testYear 测试年度
     * @return 成绩 优秀 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "WHERE tas.testYear = :testYear " +
            "AND tas.totalScore >= 90.0")
    Long countExcellentStudentsByCityAndYear(@Param("testYear") Integer testYear);


    /**
     * 查询成都市某一年度的体测成绩 良好 的学生人数
     * @param testYear 测试年度
     * @return 成绩 良好 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "WHERE tas.testYear = :testYear " +
            "AND tas.totalScore >= 80.0 AND tas.totalScore <= 89.9")
    Long countGoodStudentsByCityAndYear(@Param("testYear") Integer testYear);

    /**
     * 查询成都市某一年度的体测成绩 合格 的学生人数
     * @param testYear 测试年度
     * @return 成绩 合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "WHERE tas.testYear = :testYear " +
            "AND tas.totalScore >= 60.0 AND tas.totalScore <= 79.9")
    Long countPassingStudentsByCityAndYear(@Param("testYear") Integer testYear);


    /**
     * 查询成都市某一年度的体测成绩 不合格 的学生人数
     * @param testYear 测试年度
     * @return 成绩 不合格 的学生人数
     */
    @Query("SELECT COUNT(DISTINCT tas.studentId) " +
            "FROM TestAssessmentSummary tas " +
            "WHERE tas.testYear = :testYear " +
            "AND tas.totalScore <= 59.9")
    Long countFailingStudentsByCityAndYear(@Param("testYear") Integer testYear);


    /**
     * 根据学生id和测试年度查询某学生体测记录
     * @param studentId 学生id
     * @param currentYear 年份
     * @return 体测记录详情
     */
    TestAssessmentSummary findByStudentIdAndTestYear(Long studentId,Integer currentYear);


}